iT邦幫忙

2021 iThome 鐵人賽

DAY 4
0
自我挑戰組

從Agile PLM 移轉 Aras PLM大小事系列 第 4

4.移轉 Aras PLM大小事-Agile 匯出 Part & BOM (2)

  • 分享至 

  • xImage
  •  

第4話 Agile 匯出 Part & BOM (2)

本篇講解如何匯出BOM表
主料BOM表要匯出的表格如下
父階料號 | 子階料號 | 序號 | 用量 | Alternative | reference_designator|cn_bom_note
------------- | -------------

select
        item_par.item_number source_id, ---父階料號
        bom.item_number related_id, ---子階料號
        bom.find_number sort_order, ---序號
        NVL(bom.quantity,'1') quantity,  ---用量
        NVL(bom.text01,' ') alternative, ---替代料順序
        (select  rtrim(xmlagg(xmlelement(e, label, ',')).extract('//text()').getclobval(), ',') 
            from refdesig  r 
            where r.bom = bom.id) reference_designator ---組成用量
        ,bom.text02 cn_attrition_rate ---自訂損耗率
        ,( select  f.text
            from agile_flex f 
            where f.row_id = bom.id and f.attid =1036
        ) cn_bom_note ---備註
from bom bom
inner join item item_par on item_par.id = bom.item
inner join rev rev_p on rev_p.item = bom.item
inner join nodetable nb on nb.id = item_par.subclass
where
NVL (bom.change_out, 0) = 0
AND (SUBSTR (bom.flags, 5, 1) = 1)
AND (bom.text01 is null OR Trim(bom.text01)= 'R1')
AND rev_p.latest_flag = 1
AND rev_p.release_date >= TO_DATE('2008-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND rev_p.release_date <= TO_DATE('2008-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
order by bom.item,lpad( bom.find_number, 3 )
  • 各個欄位如何搜索可參考Select,這裡說明Where條件
語法 說明
NVL (bom.change_out, 0) = 0 料號是否變更其他料號或刪除,0沒有、1已變更
(SUBSTR (bom.flags, 5, 1) = 1) 1代表最新版
rev_p.latest_flag = 1 料號是最新版
release_date 設定在料號的發行日期,介於一年內資料

BOM替代料匯出

select
        item_par.item_number source_id,
        (
            select bom.item_number from bom 
            where
            NVL (bom.change_out, 0) = 0
            AND (SUBSTR (bom.flags, 5, 1) = 1)
            AND bom.item = alterBOM.item
            AND bom.find_number = alterbom.find_number
            AND bom.text01 = 'R1'
        ) main_part,
        alterBOM.item_number related_id,
        alterBOM.find_number sort_order,
        to_number(SPLITSTR(alterBOM.text01,2,'R'))-1   alterantive,
        NVL(alterbom.quantity,'1') quantity
        ,alterBOM.text02 cn_attrition_rate
        ,( select  f.text
            from agile_flex f 
            where f.row_id = alterBOM.id and f.attid =1036
        ) cn_bom_note
    from bom alterBOM
    inner join item item_par on item_par.id = alterBOM.item
    inner join rev rev_p on rev_p.item = alterBOM.item
       inner join nodetable nb on nb.id = item_par.subclass
    where
    NVL (alterBOM.change_out, 0) = 0
    AND (SUBSTR (alterBOM.flags, 5, 1) = 1)
    AND alterbom.text01 <> 'R1'
    AND alterbom.text01 is not null
    AND rev_p.release_date >= TO_DATE('2005-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.release_date <= TO_DATE('2009-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    AND rev_p.LATEST_FLAG = 1 
    order by item_par.item_number,lpad( alterBOM.find_number, 3 )
  • Where 條件的Alternative必須跳過R1、空白
  • to_number(SPLITSTR(alterBOM.text01,2,'R'))-1 計算R的順序

可參考其他文章說明
https://www.cnblogs.com/namelessmyth/p/13042193.html


上一篇
3.移轉 Aras PLM大小事-Agile 匯出 Part & BOM (1)
下一篇
5.移轉 Aras PLM大小事-Agile 匯出 Document
系列文
從Agile PLM 移轉 Aras PLM大小事30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言